/**
 * 
 */
package server.db;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Date;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

import org.apache.log4j.Logger;

import server.config.ServerConfig;
import server.container.AlarmierteSchleife;
import server.container.EinsatzAlarm;
import server.container.Feuerwehr;
import server.container.JPushInfo;
import server.container.SMSAlamierung;
import server.container.Schleife;

/**
 * @author Christian
 * 
 */
public class SQL {

	private static Logger logger = Logger.getLogger(SQL.class);

	private static ConnectionPool conPool = null;

	private static SQL sql = null;

	private ServerConfig config = ServerConfig.getInstance();

	/**
	 * 
	 * @throws Exception
	 */
	private SQL() throws IOException {

		if (conPool == null) {
			conPool = new ConnectionPool("jdbc:mysql://" + config.getDbHost()
					+ "/" + config.getDbName(), config.getDbUserName(),
					config.getDbPassword());
		}
	}

	// private Connection getCon() {
	//
	// logger.trace("getCon()");
	// try {
	// if (con == null || con.isClosed() || !con.isValid(10)) {
	// Class.forName("com.mysql.jdbc.Driver");
	// String connectionUrl = + "?" + "user=" +
	// + "&password=" + + "";
	// con = DriverManager.getConnection(connectionUrl);
	// // con test
	// if (!con.isValid(10)) {
	// logger.debug("SQL Verbindung aufgebaut fehlgeschlagen");
	// } else {
	// logger.debug("SQL Verbindung aufgebaut");
	// }
	// }
	// } catch (Exception e) {
	// logger.error("SQL Verbindung nicht aufgebaut", e);
	// throw new RuntimeException(e);
	// }
	// return con;
	// }

	/**
	 * 
	 * @return
	 */
	public static SQL getInstance() {

		if (sql == null) {
			logger.trace("getInstance()");
			try {
				sql = new SQL();
			} catch (Exception e) {
				logger.error("getInstance()", e);
				throw new RuntimeException(e);
			}
		}
		return sql;
	}

	/**
	 * 
	 * @param schleife
	 * @return
	 * @throws SQLException
	 */
	public static List<SMSAlamierung> getSMSAlarmList(EinsatzAlarm alarm) {

		logger.trace("getNummernBySchleife()");
		List<SMSAlamierung> list = new ArrayList<SMSAlamierung>();
		String sql = "SELECT user.id as id, user.kosten as kosten, user.bezahlt as bezahlt,"
				+ "zugehoerigkeit.statusJ as statusJ, zugehoerigkeit.statusE as statusE, zugehoerigkeit.statusF as statusF, zugehoerigkeit.statusP as statusP, "
				+ "zugehoerigkeit.statusH as statusH, zugehoerigkeit.statusC as statusC, zugehoerigkeit.pocsag as pocsag, zugehoerigkeit.montag as montag, zugehoerigkeit.dienstag as dienstag, "
				+ "zugehoerigkeit.mittwoch as mittwoch, zugehoerigkeit.donnerstag as donnerstag, zugehoerigkeit.freitag as freitag, zugehoerigkeit.samstag as samstag, "
				+ "zugehoerigkeit.sonntag as sonntag, zugehoerigkeit.vonstunde as vonstunde, zugehoerigkeit.vonminute as vonminute, zugehoerigkeit.bisstunde as bisstunde, "
				+ "zugehoerigkeit.bisminute as bisminute, zugehoerigkeit.statusA as statusA, zugehoerigkeit.einsatz5ton as einsatz5ton,"
				+ " user.absender as absender, user.nummer as nummer, zugehoerigkeit.smsmode as smsmode FROM feuerwehren JOIN user JOIN zugehoerigkeit  WHERE (feuerwehren.id = '"
				+ alarm.getFeuerwehrID()
				+ "' AND feuerwehren.id = zugehoerigkeit.feuerwehrid AND user.id = zugehoerigkeit.userid AND user.aktive = 1 AND user.nummerchecked)";
		Statement stmt = null;
		Connection con = null;
		try {
			con = conPool.getConnectionFromPool();
			stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			
			while (rs.next()) {

				// statusfiltern
				if (alarm.getEinsatzArt().equals(EinsatzAlarm.PROBEALARM)) {
					if (!rs.getBoolean("statusJ")) {
						continue;
					}
				}
				if (alarm.getEinsatzArt().equals(EinsatzAlarm.BRANDEINSATZ)) {
					if (!rs.getBoolean("statusF")) {
						continue;
					}
				}
				if (alarm.getEinsatzArt().equals(EinsatzAlarm.PERSONENRETTUNG)) {
					if (!rs.getBoolean("statusP")) {
						continue;
					}
				}
				if (alarm.getEinsatzArt().equals(EinsatzAlarm.BMA)) {
					if (!rs.getBoolean("statusE")) {
						continue;
					}
				}
				if (alarm.getEinsatzArt().equals(EinsatzAlarm.GEFAHRGUTEINSATZ)) {
					if (!rs.getBoolean("statusC")) {
						continue;
					}
				}
				if (alarm.getEinsatzArt().equals(EinsatzAlarm.ANRUFEN)) {
					if (!rs.getBoolean("statusA")) {
						continue;
					}
				}
				if (alarm.getEinsatzArt().equals(EinsatzAlarm.EINSATZ5TON)) {
					if (!rs.getBoolean("einsatz5ton")) {
						continue;
					}
				}
				if (alarm.getEinsatzArt().equals(EinsatzAlarm.POCSAG)) {
					if (!rs.getBoolean("pocsag")) {
						continue;
					}
				}

				DateFormat dfmt = new SimpleDateFormat("E");
				String tag = dfmt.format(new Date(alarm.getAlarmTime())); // Sa
				DateFormat dfmt2 = new SimpleDateFormat("HH"); // "E.', den' dd.MM.yy 'um' hh:mm:ss"
				int stunde = Integer.parseInt(dfmt2.format(new Date(alarm
						.getAlarmTime())));
				DateFormat dfmt3 = new SimpleDateFormat("mm");
				int minute = Integer.parseInt(dfmt3.format(new Date(alarm
						.getAlarmTime())));
				if (tag.equals("Mo")) {
					if (!rs.getBoolean("montag")) {
						continue;
					}
				}
				if (tag.equals("Di")) {
					if (!rs.getBoolean("dienstag")) {
						continue;
					}
				}
				if (tag.equals("Mi")) {
					if (!rs.getBoolean("mittwoch")) {
						continue;
					}
				}
				if (tag.equals("Do")) {
					if (!rs.getBoolean("donnerstag")) {
						continue;
					}
				}
				if (tag.equals("Fr")) {
					if (!rs.getBoolean("freitag")) {
						continue;
					}
				}
				if (tag.equals("Sa")) {
					if (!rs.getBoolean("samstag")) {
						continue;
					}
				}
				if (tag.equals("So")) {
					if (!rs.getBoolean("sonntag")) {
						continue;
					}
				}
				if (rs.getInt("vonstunde") > stunde
						|| stunde > rs.getInt("bisstunde")) {
					continue; // skip
				}
				if (rs.getInt("vonstunde") == stunde) {

					if (rs.getInt("vonminute") > minute) {
						continue; // skip
					}
				}

				if (rs.getInt("bisstunde") == stunde) {

					if (rs.getInt("bisminute") < minute) {
						continue; // skip
					}
				}

				/** FILTERNENDE */
				double kontoStand = rs.getDouble("bezahlt")
						- rs.getDouble("kosten");
				kontoStand = Math.round(kontoStand * 100) / 100.0;
				list.add(new SMSAlamierung(rs.getInt("id"), rs
						.getString("nummer"), alarm, rs.getString("smsmode"),
						rs.getString("absender"), kontoStand + ""));
			}
		} catch (SQLException e) {
			logger.error("SQLException", e);
		} finally {
			try {
				stmt.close();
			} catch (SQLException e) {
				logger.error("", e);
			}
			conPool.returnConnectionToPool(con);
		}
		logger.trace("Liste Sortieren");
		Collections.sort(list);
		logger.trace("doppelte entfernen");
		for (int i = 1; i < list.size(); i++) {
			if (list.get(i - 1).getNummer().equals(list.get(i).getNummer())) {
				list.remove(i - 1);
				i--;
			}
		}
		return list;
	}

	public void addKosten(String nummer, String kosten) {

		logger.trace("addKosten");
		// Steuer mitloggen
		kosten = (Double.parseDouble(kosten) * 1.19) + "";
		String sql = "UPDATE user SET kosten = kosten + '" + kosten
				+ "' WHERE nummer = '" + nummer + "'";
		Statement stmt;
		Connection con = null;
		try {
			con = conPool.getConnectionFromPool();
			stmt = con.createStatement();
			stmt.executeUpdate(sql);
			stmt.close();
		} catch (SQLException e) {
			logger.error("SQLException", e);
		}
		String sql2 = "SELECT id FROM user WHERE nummer = '" + nummer + "'";
		Statement stmt2;
		String userid = "";
		try {
			stmt2 = con.createStatement();
			ResultSet rs2 = stmt2.executeQuery(sql2);
			while (rs2.next()) {
				userid = rs2.getString("id");
			}
			stmt2.close();
		} catch (SQLException e) {
			logger.error("SQLException", e);
		}
		String sql3 = "INSERT INTO kosten SET user='" + userid
				+ "', kosten = '" + kosten + "'";
		Statement stmt3 = null;
		try {
			stmt3 = con.createStatement();
			stmt3.executeUpdate(sql3);
			stmt3.close();
		} catch (SQLException e) {
			logger.error("SQLException", e);
		} finally {
			try {
				stmt3.close();
			} catch (SQLException e) {
				logger.error("", e);
			}
			conPool.returnConnectionToPool(con);
		}
	}

	public static void alarmierungInDBEintragen(final EinsatzAlarm alarm) {

		logger.trace("alarmierungLoggen");

		PreparedStatement stmt1 = null;
		Connection con = null;
		try {
			int feuerwehrId = -1;
			if (FeuerwehrCache.getFeuerwehrBySchleife(alarm.getSchleife()) != null) {
				feuerwehrId = FeuerwehrCache.getFeuerwehrBySchleife(
						alarm.getSchleife()).getId();
			}
			con = conPool.getConnectionFromPool();
			stmt1 = con
					.prepareStatement("INSERT INTO alarmierung SET alarmkennung = ?, typ = ?, status = ?, einsatzart = ?, rictext = ?, feuerwehrid = ?");

			stmt1.setString(1, alarm.getSchleife());
			stmt1.setString(2, alarm.getType());
			stmt1.setString(3, alarm.getStatus());
			stmt1.setString(4, alarm.getEinsatzArt());
			stmt1.setString(5, "");
			stmt1.setInt(6, feuerwehrId);
			stmt1.executeUpdate();
			
			
		} catch (SQLException e) {
			logger.error("SQLException", e);
		} finally {
			try {
				stmt1.close();
			} catch (SQLException e) {
				logger.error("", e);
			}
			conPool.returnConnectionToPool(con);
		}
		
		SQL.updateAlarmCacheByFeuerwehrId(alarm.getFeuerwehrID()+"", System.currentTimeMillis(), alarm.getSchleife());
	}
	
	public static void removeDebugAlarme() {

		Connection con = null;
		PreparedStatement stmt1 = null;
		try {

			con = conPool.getConnectionFromPool();
			stmt1 = con
					.prepareStatement("DELETE FROM alarmierungscache WHERE debug = ?");

			stmt1.setInt(1, 1);
			stmt1.executeUpdate();
			stmt1.close();
		} catch (SQLException e) {
			logger.error("SQLException", e);
		} finally {

			try {
				stmt1.close();
			} catch (SQLException e) {
				logger.error("", e);
			}
			conPool.returnConnectionToPool(con);
		}

		try {

			con = conPool.getConnectionFromPool();
			stmt1 = con
					.prepareStatement("DELETE FROM alarmierungen WHERE debug = ?");

			stmt1.setInt(1, 1);
			stmt1.executeUpdate();
			stmt1.close();
		} catch (SQLException e) {
			logger.error("SQLException", e);
		} finally {

			try {
				stmt1.close();
			} catch (SQLException e) {
				logger.error("", e);
			}
			conPool.returnConnectionToPool(con);
		}
	}

	public static List<JPushInfo> getPushAlarmInfos(EinsatzAlarm alarm) {

		String feuerwehrId = alarm.getFeuerwehrID() + "";
		List<JPushInfo> list = new ArrayList<JPushInfo>();
		String sql = "SELECT prioritaet, userid, typ FROM prowlzuordnung WHERE feuerwehrid = '"
				+ feuerwehrId + "' AND " + "status='aktive'";
		Connection con = null;
		Statement stmt = null;
		try {

			con = conPool.getConnectionFromPool();
			stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				int userid = rs.getInt("userid");
				String prowlId = getAPIKeyByUserId(userid + "");
				String prioritaet = rs.getString("prioritaet");
				if (!prowlId.equals("")) {
					if(rs.getString("typ").equals("Prowl")) {
						list.add(new JPushInfo(userid, alarm, prowlId, prioritaet, JPushInfo.PROWL));
					}
					else if(rs.getString("typ").equals("Notifymyandroid")) {
						list.add(new JPushInfo(userid, alarm, prowlId, prioritaet, JPushInfo.NOTIFYMYANDROID));
					}
				}
			}
		} catch (SQLException e) {
			logger.error("SQLException", e);
		} finally {
			try {
				stmt.close();
			} catch (SQLException e) {
				logger.error("", e);
			}
			conPool.returnConnectionToPool(con);
		}
		return list;
	}

	public static String getFeuerwehrTypeByID(int id) {

		String sql = "SELECT type FROM feuerwehren WHERE id = '" + id + "'";
		Connection con = null;
		Statement stmt = null;
		try {

			con = conPool.getConnectionFromPool();
			stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				return rs.getString("type");
			}
		} catch (SQLException e) {
			logger.error("SQLException", e);
		} finally {
			try {
				stmt.close();
			} catch (SQLException e) {
				logger.error("", e);
			}
			conPool.returnConnectionToPool(con);
		}
		return "Unbekannt";
	}

	/**
	 * 
	 * @param schleife
	 * @return
	 */

	public static int getFeuerwehrIdByAlarmValue(String schleife) {

		String sql = "SELECT feuerwehrid FROM feuerwehrkennungen WHERE value = '"
				+ schleife + "'";
		Connection con = null;
		Statement stmt = null;
		try {

			con = conPool.getConnectionFromPool();
			stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				return rs.getInt("feuerwehrid");
			}
		} catch (SQLException e) {
			logger.error("SQLException", e);
		} finally {
			try {
				stmt.close();
			} catch (SQLException e) {
				logger.error("", e);
			}
			conPool.returnConnectionToPool(con);
		}
		return -1;
	}

	/**
	 * 
	 * @param schleife
	 * @return
	 */
	public static String getAPIKeyByUserId(String userid) {

		String sql = "SELECT pushApiKey FROM user WHERE id = '" + userid
				+ "' AND (prowl = '1' OR notifymyandroid = '1')";
		Connection con = null;
		Statement stmt = null;
		try {

			con = conPool.getConnectionFromPool();
			stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				return rs.getString("pushApiKey");
			}
		} catch (SQLException e) {
			logger.error("SQLException", e);
		} finally {
			try {
				stmt.close();
			} catch (SQLException e) {
				logger.error("", e);
			}
			conPool.returnConnectionToPool(con);
		}
		return "";
	}

	/**
	 * 
	 * @param schleife
	 * @return
	 */
	public static String getNotifryKeyByUserId(String userid) {

		String sql = "SELECT notifryKey FROM user WHERE id = '" + userid
				+ "' AND notifry = '1'";
		Connection con = null;
		Statement stmt = null;
		try {

			con = conPool.getConnectionFromPool();
			stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				return rs.getString("notifryKey");
			}
		} catch (SQLException e) {
			logger.error("SQLException", e);
		} finally {
			try {
				stmt.close();
			} catch (SQLException e) {
				logger.error("", e);
			}
			conPool.returnConnectionToPool(con);
		}
		return "";
	}

	/**
	 * @return
	 */
	public static void fillFeuerwehrCache() {

		String sql = "SELECT feuerwehrid, type, value FROM feuerwehrkennungen";
		Connection con = null;
		Statement stmt = null;
		try {

			con = conPool.getConnectionFromPool();
			stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {

				int fwid = rs.getInt("feuerwehrid");
				String schleife = rs.getString("value");

				Feuerwehr fw = null;
				try {
					fw = FeuerwehrCache.getFeuerwehrById(fwid);
				} catch (RuntimeException e) {

					Statement stmt2 = con.createStatement();
					String sql2 = "SELECT id, name, type FROM feuerwehren WHERE id = '"
							+ fwid + "'";
					ResultSet rs2 = stmt2.executeQuery(sql2);
					while (rs2.next()) {

						fw = new Feuerwehr();
						fw.setName(rs2.getString("name"));
						fw.setId(rs2.getInt("id"));
						fw.setTyp(rs2.getString("type"));
					}
					stmt2.close();
				}

				Schleife newSchleife = new Schleife();
				newSchleife.setTyp(rs.getString("type"));
				newSchleife.setSchleife(schleife);
				fw.addSchleife(newSchleife);
				FeuerwehrCache.addFeuerwehr(schleife, fw);

			}
		} catch (SQLException e) {
			logger.error("SQLException", e);
		} finally {
			try {
				stmt.close();
			} catch (SQLException e) {
				logger.error("", e);
			}
			conPool.returnConnectionToPool(con);
		}
	}

	public static void setNumberInValid(SMSAlamierung smsAlarmierung) {

		String sql = "UPDATE user SET nummerchecked = '0' WHERE id = '"
				+ smsAlarmierung.getUserId() + "'";
		Connection con = null;
		Statement stmt = null;
		try {

			con = conPool.getConnectionFromPool();
			stmt = con.createStatement();
			stmt.executeUpdate(sql);
		} catch (SQLException e) {
			logger.error("SQLException", e);
		} finally {
			try {
				stmt.close();
			} catch (SQLException e) {
				logger.error("", e);
			}
			conPool.returnConnectionToPool(con);
		}
	}

	public ConnectionPool getConPool() {
		return conPool;
	}

	public static void fillCacheTableNeu(int abdatensatz) {

		Connection con = null;
		PreparedStatement stmt = null;
		try {

			con = conPool.getConnectionFromPool();

			// Neuen Eintrag erstellen
			stmt = con
					.prepareStatement("SELECT id, UNIX_TIMESTAMP(`timestamp`) as timestamp, feuerwehrid, alarmkennung FROM alarmierung AS a ORDER BY TIMESTAMP ASC ");

			ResultSet rs = stmt.executeQuery();

			Set<Integer> set = new HashSet<>();

			int count = 0;
			long temptime = System.currentTimeMillis();
			long t1 = System.currentTimeMillis();
			while (rs.next()) {

				
				if(rs.getInt("id") < abdatensatz) {
					continue;
				}
				
				if (set.contains(rs.getInt("id"))) {
					continue;
				}
				
				
				if(count > 50) {
				logger.info(rs.getInt("id"));
				logger.info("time " + ((System.currentTimeMillis() - temptime))+ "  geteilt " + ((System.currentTimeMillis() - temptime))/50);
				temptime = System.currentTimeMillis();
					count = 0;
				}
				count++;
				List<Integer> l = SQL.updateAlarmCacheByFeuerwehrId(
						rs.getString("feuerwehrid"), rs.getLong("timestamp") * 1000, rs.getString("alarmkennung"));

				for (Integer i : l) {
					set.add(i);
				}
			}
			
			logger.info("GesamtZeit " + ((System.currentTimeMillis() - t1) / 1000.0 / 60.0) + " mins");

		} catch (SQLException e) {
			logger.error("SQLException", e);
		} finally {
			try {
				stmt.close();
			} catch (SQLException e) {
				logger.error("", e);
			}
			conPool.returnConnectionToPool(con);
		}
	}

	public static List<Integer> updateAlarmCacheByFeuerwehrId(
			String feuerwehrId, long startpunkt, String alarmkennung) {

//		long t1 = System.nanoTime();
		Connection con = null;
		PreparedStatement stmt = null;
		List<AlarmierteSchleife> list = new ArrayList<>();


		List<Integer> usedList = new ArrayList<>();
		
		if(alarmkennung.startsWith("058") || alarmkennung.startsWith("059")) {
			return usedList;
		}
		
		try {
			con = conPool.getConnectionFromPool();
			
			
			String sql = "";
			if(!feuerwehrId.equals("-1")) {
				
				sql = "DELETE FROM `alarmierungsshowcache` WHERE TIMESTAMP > DATE_ADD(?, INTERVAL -30 Minute ) AND TIMESTAMP < DATE_ADD(?, INTERVAL +30 Minute ) AND feuerwehrid = ? ORDER BY TIMESTAMP DESC";	
				// Alten Eintrag löschen
				PreparedStatement stmtdel = null;
				stmtdel = con
						.prepareStatement(sql);
	
				stmtdel.setTimestamp(1, new Timestamp(startpunkt));
				stmtdel.setTimestamp(2, new Timestamp(startpunkt));
				stmtdel.setString(3, feuerwehrId);
				
//				System.out.println(stmtdel);
				stmtdel.execute();
				stmtdel.close();
			
			}
			
			String sql2 = "";
			if(feuerwehrId.equals("-1")) {
				sql2 = "SELECT id, alarmkennung, feuerwehrid, UNIX_TIMESTAMP(`timestamp`) as timestamp, typ, status, einsatzart, rictext FROM alarmierung AS a WHERE  TIMESTAMP > DATE_ADD(?, INTERVAL -30 Minute ) AND TIMESTAMP < DATE_ADD(?, INTERVAL +30 Minute ) AND feuerwehrid = ? AND alarmkennung = ? ORDER BY TIMESTAMP  DESC ";
			}
			else {
				sql2 = "SELECT id, alarmkennung, feuerwehrid, UNIX_TIMESTAMP(`timestamp`) as timestamp, typ, status, einsatzart, rictext FROM alarmierung AS a WHERE  TIMESTAMP > DATE_ADD(?, INTERVAL -30 Minute ) AND TIMESTAMP < DATE_ADD(?, INTERVAL +30 Minute ) AND feuerwehrid = ?  ORDER BY TIMESTAMP  DESC";
			}
			// Neuen Eintrag erstellen
			stmt = con
					.prepareStatement(sql2);

			
			stmt.setTimestamp(1, new Timestamp(startpunkt));
			stmt.setTimestamp(2, new Timestamp(startpunkt));
			stmt.setString(3, feuerwehrId);
			if(feuerwehrId.equals("-1")) {
				
				stmt.setString(4, alarmkennung);
			}

			ResultSet rs = stmt.executeQuery();
//			System.out.println(stmt);

			while (rs.next()) {

				String feuerwehrTyp = FeuerwehrCache
						.getFeuerwehrTypByAlarmkennung(rs
								.getString("alarmkennung"));
				String alarmTyp = FeuerwehrCache.getAlarmTypByAlarmkennung(rs
						.getString("alarmkennung"));

				list.add(new AlarmierteSchleife(feuerwehrTyp, rs
						.getString("alarmkennung"), rs.getString("einsatzart"),
						rs.getString("rictext"), rs.getLong("timestamp"),
						alarmTyp));

				usedList.add(rs.getInt("id"));
			}

		} catch (SQLException e) {
			logger.error("SQLException", e);
		} finally {
			try {
				if(stmt != null) {
					stmt.close();
				}
			} catch (SQLException e) {
				logger.error("", e);
			}
		}
		
		// Liste sortieren
		Collections.sort(list);
		
		for(int i = 1;i < list.size();i++) {
			
			if(list.get(i - 1).getAlarmKennung().equals(list.get(i).getAlarmKennung())) {
				list.remove(i);
				i--;
			}
		}

		long alarmTimestamp = 0;
		String datum = "";
		String uhrzeit = "";
		String einsatzArt = "";
		String alarmKennung = "";
		String rictext = "";
		String feuerwehrType = "";
		String feuerwehrName = "";

		if (list.size() == 0) {

			logger.info("Kein Eintrag gefunden");
			conPool.returnConnectionToPool(con);
			return usedList;
		}

		String lastKennung = "";
		
		double erstePocsagSchleifeNach = Double.MAX_VALUE;
		
		double ersteNichtPocsagSchleife = Double.MAX_VALUE;
		
		for (AlarmierteSchleife s : list) {
			
			if(!lastKennung.equals("") && s
					.getAlarmKennung().equals(lastKennung)) {
				continue;
			}

			if (einsatzArt.equals("")) {
				einsatzArt = s.getEinsatzart();
			}

			if (rictext.equals("")) {
				rictext = s.getRicText();
			}

			if (feuerwehrType.equals("")) {
				feuerwehrType = s.getFeuerwehrType();
			}

			if (feuerwehrName.equals("")) {

				Feuerwehr fw = FeuerwehrCache.getFeuerwehrBySchleife(s
						.getAlarmKennung());
				if (fw != null) {
					feuerwehrName = fw.getName();
				}
			}

			if (datum.equals("") || s.getTimestamp() * 1000 < alarmTimestamp) {
				alarmTimestamp = s.getTimestamp();
				alarmTimestamp *= 1000;
				DateFormat dfmt = new SimpleDateFormat("dd.MM.yy");
				datum = (dfmt.format(alarmTimestamp));
				DateFormat dfmt2 = new SimpleDateFormat("HH:mm:ss");
				uhrzeit = (dfmt2.format(alarmTimestamp));
			}
			
			if(s.getAlarmKennung().startsWith("086") && erstePocsagSchleifeNach > s.getTimestamp()) {
				erstePocsagSchleifeNach = s.getTimestamp();
			}
			else if(ersteNichtPocsagSchleife > s.getTimestamp()) {
				ersteNichtPocsagSchleife = s.getTimestamp();
			}
			
			lastKennung = s
					.getAlarmKennung();
			alarmKennung += s.getAlarmKennung() + " ";
		}
		
		if(erstePocsagSchleifeNach != Double.MAX_VALUE && ersteNichtPocsagSchleife != Double.MAX_VALUE) {
		
			alarmKennung += " Pocsagdelay " + (erstePocsagSchleifeNach - ersteNichtPocsagSchleife) + "s ";
		}
		PreparedStatement stmt3 = null;
		
		try {
			
			stmt3 = con
					.prepareStatement("INSERT INTO `alarmierungsshowcache` SET `einsatzart`=?, `uhrzeit`=?, `datum`=?, `feuerwehrname`=?, `alarmkennungen`=?, `rictext`=?, `feuerwehrtype`=?, `timestamp`=?, `debug`=?, `feuerwehrId`=?");

			stmt3.setString(1, einsatzArt);
			stmt3.setString(2, uhrzeit);
			stmt3.setString(3, datum);
			stmt3.setString(4, feuerwehrName);
			stmt3.setString(5, alarmKennung);
			stmt3.setString(6, rictext);
			stmt3.setString(7, feuerwehrType);
			stmt3.setTimestamp(8, new Timestamp(alarmTimestamp));
			stmt3.setBoolean(9, false);
			stmt3.setString(10, feuerwehrId);
			stmt3.executeUpdate();

		} catch (SQLException e) {
			logger.error("SQLException", e);
		} finally {
			try {
				stmt3.close();
			} catch (SQLException e) {
				logger.error("", e);
			}
			conPool.returnConnectionToPool(con);

		}
//		logger.info("gesamt " + (System.nanoTime() - t1) / 1000000);			
		
		return usedList;
	}

	public static void unbekannteEinraegeClearen() {
	
		try {
			Connection con = conPool.getConnectionFromPool();
			
			
			String sql = "";
		
				
				sql = "DELETE FROM `alarmierungsshowcache` WHERE feuerwehrid = ?";	
				// Alten Eintrag löschen
				PreparedStatement stmtdel = null;
				stmtdel = con
						.prepareStatement(sql);
	
				stmtdel.setString(1, "-1");
				
//				System.out.println(stmtdel);
				stmtdel.execute();
				stmtdel.close();
			
			} catch (Exception e) {
				e.printStackTrace();
			}
			
	}

//	public static void fillNewAlarmiungTable() {
//
//		PreparedStatement stmt1 = null;
//		Connection con = null;
//
//		PreparedStatement stmt2 = null;
//		try {
//
//			con = conPool.getConnectionFromPool();
//
//			String sql2 = "SELECT alarmkennung, status, einsatzart, timestamp FROM alarmierteschleifen ORDER BY timestamp ASC";
//			stmt2 = con.prepareStatement(sql2);
//			ResultSet rs2 = stmt2.executeQuery();
//			stmt1 = con
//					.prepareStatement("INSERT INTO alarmierung SET alarmkennung = ?, status = ?, einsatzart = ?, rictext = ?, feuerwehrid = ?, timestamp = ?");
//			while (rs2.next()) {
//
//				int feuerwehrId = -1;
//				Feuerwehr fw = FeuerwehrCache.getFeuerwehrBySchleife(rs2
//						.getString("alarmkennung"));
//				if (fw != null) {
//					feuerwehrId = fw.getId();
//				}
//				logger.info(rs2.getString("alarmkennung"));
//				if (rs2.getString("alarmkennung").startsWith("058")
//						|| rs2.getString("alarmkennung").startsWith("057")) {
//					continue;
//				}
//
//				String status = rs2.getString("status");
//				int status2 = -1;
//				try {
//					status2 = Integer.parseInt(status);
//				} catch (Exception e) {
//					// logger.error("", e);
//				}
//
//				stmt1.setString(1, rs2.getString("alarmkennung"));
//				stmt1.setInt(2, status2);
//				stmt1.setString(3, rs2.getString("einsatzart"));
//				stmt1.setString(4, "");
//				stmt1.setInt(5, feuerwehrId);
//				stmt1.setTimestamp(6, rs2.getTimestamp("timestamp"));
//				stmt1.executeUpdate();
//			}
//			stmt1.close();
//
//		} catch (SQLException e) {
//			logger.error("SQLException", e);
//		} finally {
//			try {
//				stmt2.close();
//			} catch (SQLException e) {
//				logger.error("", e);
//			}
//		}
//
//		conPool.returnConnectionToPool(con);
//	}
}
